package edu.zut.dao.impl;

import edu.zut.dao.EmpDao;
import edu.zut.pojo.Employee;
import edu.zut.pojo.Page;
import edu.zut.util.DBUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//员工信息的数据层实现类
public class EmpDaoImpl implements EmpDao {


    @Override
    public List<Employee> selectEmpAll() {
        String sql = "select e.*,d.dep_name from department d,employee e where d.id = e.dep_id";
        ResultSet rs = DBUtils.query(sql, null);
        List<Employee> list = new ArrayList<>();
        try {
            while (rs.next()) {
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setDep_name(rs.getString("dep_name"));
                employee.setSex(rs.getString("sex"));
                employee.setSchool(rs.getString("school"));
                employee.setPhone(rs.getString("phone"));
                employee.setName(rs.getString("name"));
                employee.setMajor(rs.getString("major"));
                employee.setDuty(rs.getString("duty"));
                employee.setEdu(rs.getString("edu"));
                employee.setEntry_time(rs.getDate("entry_time"));
                employee.setAge(rs.getInt("age"));
                employee.setAddress(rs.getString("address"));
                list.add(employee);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    //根据员工姓名查询
    @Override
    public List<Employee> selectEmpByName(String emp_name) {
        String sql = "select e.*,d.dep_name from department d,employee e " +
                "where d.id = e.dep_id and name like ?";
        ResultSet rs = DBUtils.query(sql, '%' + emp_name + '%');
        List<Employee> list = new ArrayList<>();
        try {
            while (rs.next()) {
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setDep_name(rs.getString("dep_name"));
                employee.setSex(rs.getString("sex"));
                employee.setSchool(rs.getString("school"));
                employee.setPhone(rs.getString("phone"));
                employee.setName(rs.getString("name"));
                employee.setMajor(rs.getString("major"));
                employee.setDuty(rs.getString("duty"));
                employee.setEdu(rs.getString("edu"));
                employee.setEntry_time(rs.getDate("entry_time"));
                employee.setAge(rs.getInt("age"));
                employee.setAddress(rs.getString("address"));
                list.add(employee);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int deleteById(int id) {
        String sql = "delete from employee where id=?";
        int i = DBUtils.update(sql, id);
        return i;
    }


    @Override
    public Page<Employee> pageQuery(Page page) {
        String sql = "select e.*,d.dep_name from department d,employee e where d.id = e.dep_id limit ?,?";
        ResultSet rs = DBUtils.query(sql, page.getCurrentPage(), page.getPageSize());
        List<Employee> list = new ArrayList<>();
        try {
            while (rs.next()) {
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setDep_name(rs.getString("dep_name"));
                employee.setSex(rs.getString("sex"));
                employee.setSchool(rs.getString("school"));
                employee.setPhone(rs.getString("phone"));
                employee.setName(rs.getString("name"));
                employee.setMajor(rs.getString("major"));
                employee.setDuty(rs.getString("duty"));
                employee.setEdu(rs.getString("edu"));
                employee.setEntry_time(rs.getDate("entry_time"));
                employee.setAge(rs.getInt("age"));
                employee.setAddress(rs.getString("address"));
                list.add(employee);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        page.setList(list);
        return page;
    }

    @Override
    public int queryCount() {
        String sql = "select count(*) from employee";
        ResultSet rs = DBUtils.query(sql, null);
        int count = 0;
        try {
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }
}
